package com.platform.utils.excel;


import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * Excel2003格式解析
 *
 * @author lipengjun
 * @email 939961241@qq.com
 * @date 2017年10月28日 13:11:27
 */
public class Excel2003Reader implements HSSFListener {

  private int minColumns = -1;

  private POIFSFileSystem fs;

  /**
   * 最后一行行号
   */
  private int lastRowNumber;

  /**
   * 最后一列列号
   */
  private int lastColumnNumber;

  /**
   * Should we output the formula, or the value it has?
   */
  private boolean outputFormulaValues = true;

  /**
   * For parsing Formulas
   */
  private SheetRecordCollectingListener workbookBuildingListener;

  // 工作薄
  private HSSFWorkbook stubWorkbook;

  // Records we pick up as we process
  private SSTRecord sstRecord;

  private FormatTrackingHSSFListener formatListener;

  // 表索引
  private int sheetIndex = -1;

  private BoundSheetRecord[] orderedBSRs;

  @SuppressWarnings("rawtypes")
  private ArrayList boundSheetRecords = new ArrayList();

  // For handling formulas with string results
  private int nextRow;

  private int nextColumn;

  private boolean outputNextStringRecord;

  // 存储行记录的容器
  private List<String> rowlist = new ArrayList<String>();
  ;

  // 单Sheet数据
  private List<String[]> sheetData = new ArrayList<String[]>();

  // 多Sheet数据
  private Map<Integer, List<String[]>> workData = new HashMap<Integer, List<String[]>>();

  /**
   * 遍历excel下所有的sheet
   *
   * @param fileStream 处理文件流
   * @throws IOException 抛出IO异常
   */
  public void process(InputStream fileStream)
      throws IOException {
    this.fs = new POIFSFileSystem(fileStream);
    MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(
        this);
    formatListener = new FormatTrackingHSSFListener(listener);
    HSSFEventFactory factory = new HSSFEventFactory();
    HSSFRequest request = new HSSFRequest();
    if (outputFormulaValues) {
      request.addListenerForAllRecords(formatListener);
    } else {
      workbookBuildingListener = new SheetRecordCollectingListener(
          formatListener);
      request.addListenerForAllRecords(workbookBuildingListener);
    }
    factory.processWorkbookEvents(request, fs);
  }

  /**
   * HSSFListener 监听方法，处理 Record
   *
   * @param record 行记录
   */
  @SuppressWarnings("unchecked")
  @Override
  public void processRecord(Record record) {
    int thisRow = -1;
    int thisColumn = -1;
    String thisStr = null;
    String value = null;
    switch (record.getSid()) {
      case BoundSheetRecord.sid:
        boundSheetRecords.add(record);
        break;
      case BOFRecord.sid:
        BOFRecord br = (BOFRecord) record;
        if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
          // 如果有需要，则建立子工作薄
          if (workbookBuildingListener != null && stubWorkbook == null) {
            stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
          }
          if (sheetIndex >= 0) {
            List<String[]> data = new ArrayList<String[]>();
            data.addAll(sheetData);
            workData.put(sheetIndex, data);
            sheetData.clear();
          }
          sheetIndex++;
          if (orderedBSRs == null) {
            orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
          }
        }
        break;
      case SSTRecord.sid:
        sstRecord = (SSTRecord) record;
        break;
      case BlankRecord.sid:
        BlankRecord brec = (BlankRecord) record;
        thisRow = brec.getRow();
        thisColumn = brec.getColumn();
        thisStr = "";
        rowlist.add(thisColumn, thisStr);
        break;
      case BoolErrRecord.sid: // 单元格为布尔类型
        BoolErrRecord berec = (BoolErrRecord) record;
        thisRow = berec.getRow();
        thisColumn = berec.getColumn();
        thisStr = berec.getBooleanValue() + "";
        rowlist.add(thisColumn, thisStr);
        break;
      case FormulaRecord.sid: // 单元格为公式类型
        FormulaRecord frec = (FormulaRecord) record;
        thisRow = frec.getRow();
        thisColumn = frec.getColumn();
        if (outputFormulaValues) {
          if (Double.isNaN(frec.getValue())) {
            // Formula result is a string
            // This is stored in the next record
            outputNextStringRecord = true;
            nextRow = frec.getRow();
            nextColumn = frec.getColumn();
          } else {
            thisStr = formatListener.formatNumberDateCell(frec);
          }
        } else {
          thisStr = '"' + HSSFFormulaParser.toFormulaString(
              stubWorkbook, frec.getParsedExpression()) + '"';
        }
        rowlist.add(thisColumn, thisStr);
        break;
      case StringRecord.sid:// 单元格中公式的字符串
        if (outputNextStringRecord) {
          // String for formula
          StringRecord srec = (StringRecord) record;
          thisStr = srec.getString();
          thisRow = nextRow;
          thisColumn = nextColumn;
          outputNextStringRecord = false;
        }
        break;
      case LabelRecord.sid:
        LabelRecord lrec = (LabelRecord) record;
        thisColumn = lrec.getColumn();
        value = lrec.getValue().trim();
        value = value.equals("") ? " " : value;
        this.rowlist.add(thisColumn, value);
        break;
      case LabelSSTRecord.sid: // 单元格为字符串类型
        LabelSSTRecord lsrec = (LabelSSTRecord) record;
        thisColumn = lsrec.getColumn();
        if (sstRecord == null) {
          rowlist.add(thisColumn, " ");
        } else {
          value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
          value = value.equals("") ? " " : value;
          rowlist.add(thisColumn, value);
        }
        break;
      case NumberRecord.sid: // 单元格为数字类型
        NumberRecord numrec = (NumberRecord) record;
        thisColumn = numrec.getColumn();
        value = formatListener.formatNumberDateCell(numrec).trim();
        value = value.equals("") ? " " : value;
        // 向容器加入列值
        rowlist.add(thisColumn, value);
        break;
      default:
        break;
    }

    // 遇到新行的操作
    if (thisRow != -1 && thisRow != lastRowNumber) {
      lastColumnNumber = -1;
    }

    // 空值的操作
    if (record instanceof MissingCellDummyRecord) {
      MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
      thisColumn = mc.getColumn();
      rowlist.add(thisColumn, "");
    }

    // 更新行和列的值
    if (thisRow > -1) {
      lastRowNumber = thisRow;
    }
    if (thisColumn > -1) {
      lastColumnNumber = thisColumn;
    }

    // 行结束时的操作
    if (record instanceof LastCellOfRowDummyRecord) {
      if (minColumns > 0) {
        // 列值重新置空
        if (lastColumnNumber == -1) {
          lastColumnNumber = 0;
        }
      }
      lastColumnNumber = -1;
      // 每行结束时， 数据写入集合
      sheetData.add(rowlist.toArray(new String[]{}));
      // 清空容器
      rowlist.clear();
    }
  }

  /**
   * 获取数据(单Sheet)
   *
   * @param sheetIndex sheet下标
   * @return List<String   [   ]> 数据
   */
  public List<String[]> getSheetData(Integer sheetIndex) {
    return workData.get(sheetIndex);
  }

  /**
   * 获取数据(多Sheet)
   *
   * @return Map<Integer   ,       List   <   String   [   ]>> 多sheet的数据
   */
  public Map<Integer, List<String[]>> getSheetData() {
    return workData;
  }
}